<?php
class PersonnelChartController extends Zone_Action{
	public function initAction(){
		$posts = $this->Model->fetchAll("
			SELECT `a`.*,`b`.`username`,`b`.`ID` as `user_id`
			FROM `departments` as `a`
			LEFT JOIN `users` as `b` ON `a`.`created_by_id`=`b`.`ID`
                WHERE `a`.`parent_id`<>0
                  AND `a`.`parent_id` IS NOT NULL ORDER BY `ID`");
				  
		$this->view->departments = $this->Model->fetchAll("SELECT * FROM departments");

        $departments = $this->Model->fetchAll("SELECT `a`.*,`b`.`username`,`b`.`ID` as `user_id`
			FROM `departments` as `a`
			LEFT JOIN `users` as `b` on `a`.`created_by_id`=`b`.`ID` ORDER BY `ord`");


		$this->set(array(
			side_departments	=> $posts,
            departments         => $departments,
            skills_sp           => $this->Model->fetchAll("SELECT * FROM `personnels_degrees`")
		));
	}
	
	public function indexAction(){	
		$male = $this->Model->fetchOne("SELECT COUNT(*) FROM `personnels` WHERE is_draft='no' AND gender=0");		
		$female = $this->Model->fetchOne("SELECT COUNT(*) FROM `personnels` WHERE is_draft='no' AND gender=1");		
		$this->set(array(
			male	=> $male,
			female	=> $female
		));	
		
		//age
		$age1 = $this->Model->fetchOne("SELECT COUNT(*) FROM `personnels` WHERE is_draft='no' AND DATEDIFF(NOW(),`birthday`)/365 > 50");		
		$age2 = $this->Model->fetchOne("SELECT COUNT(*) FROM `personnels` WHERE is_draft='no' AND DATEDIFF(NOW(),`birthday`)/365 > 40 AND DATEDIFF(NOW(),`birthday`)/365 < 50 ");		
		$age3 = $this->Model->fetchOne("SELECT COUNT(*) FROM `personnels` WHERE is_draft='no' AND DATEDIFF(NOW(),`birthday`)/365 >30 AND DATEDIFF(NOW(),`birthday`)/365 < 40 ");		
		$age4 = $this->Model->fetchOne("SELECT COUNT(*) FROM `personnels` WHERE is_draft='no' AND DATEDIFF(NOW(),`birthday`)/365 >= 20 AND DATEDIFF(NOW(),`birthday`)/365 < 30 ");		
		
		$this->set(array(
			age1	=> $age1,
			age2	=> $age2,
			age3	=> $age3,
			age4	=> $age4			
		));	
		
		
		$skills = $this->Model->fetchAll("SELECT * FROM `personnels_degrees` ORDER BY `title`");
		$b = array();
		foreach( $skills as $a ){
			$b[] = array(
				label	=> $a['title'],				
				total	=> $this->Model->fetchOne("SELECT COUNT(*) 
					FROM `personnels_cvs` 
					WHERE `skill_speciality_id`='{$a['ID']}' ")		
			);
		}
		
		$this->set('skill_stats',$b);	

		$this->set(array(
			'gov'	=> $this->Model->fetchOne("SELECT COUNT(*) 
					FROM `personnels` as `a`
					LEFT JOIN `personnels_govs` as `b`
					ON `b`.`personnel_id`=`a`.`ID`
					WHERE `b`.`date_gov_reg` IS NOT NULL AND `a`.`is_draft`='no'"),
			'no_gov'	=> $this->Model->fetchOne("SELECT COUNT(*) 
					FROM `personnels` as `a`
					LEFT JOIN `personnels_govs` as `b`
					ON `b`.`personnel_id`=`a`.`ID`
					WHERE `b`.`date_gov_reg` IS NULL AND `a`.`is_draft`='no'")
		));	
	}
}
?>